package org.wk.user.dao;

import afu.org.checkerframework.checker.oigj.qual.Modifier;
import io.swagger.models.auth.In;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.wk.core.db.dao.GenericJpaRepository;
import org.wk.user.domain.UserPromoteRelation;

import java.util.List;

/**
 * 
 * @ClassName	: UserPromoteRelationDao
 * @Package		：org.wk.user.dao
 * @Author df.w 
 * @Create Time	：2018年5月2日 下午5:21:55
 * @Description	:
 */
public interface UserPromoteRelationDao extends GenericJpaRepository<UserPromoteRelation, String>{


	/**
	 * 根据推广人ID 和 被推广人ID 查询推广关系
	 * @param parentId
	 * @param userId
	 * @return
	 */
	UserPromoteRelation findByParentIdAndUserId(String parentId, String userId);

	/**
	 * 查询某个用户的所有级别的上级
	 * @param promoter
	 * @return
	 */
	List<UserPromoteRelation> findByUserId(String promoter);

	/**
	 * 查询我的团队，列出所有我的下级
	 * @param parentId
	 * @return
	 */
	Page<UserPromoteRelation> findByParentId(String parentId,Pageable pageable);

	/**
	 * 查询我的团队，列出直属上级
	 * @param parentId 上级ID
	 * @param relationship 关系级别
	 * @param pageable
	 * @return
	 */
	Page<UserPromoteRelation> findByParentIdAndRelationship(String parentId, int relationship, Pageable pageable);

	/**
	 * 查询满足申请条件最近的一名用户
	 * @param userId
	 * @param relationship
	 * @return
	 */
	@Query(nativeQuery = true,value = "select supr.* from s_user_promote_relation supr LEFT JOIN s_user su on supr.parent_id = su.id where supr.user_id = ?1 and supr.parent_id != (SELECT referral_code from s_user where id = ?1) and supr.status is null and su.identity_code >= ?2 and supr.relationship > (SELECT IFNULL(MAX(relationship),0) from s_user_promote_relation where STATUS = 'Y' and user_id = ?1) ORDER BY supr.relationship LIMIT 0,1")
	UserPromoteRelation findByUserIdAndRelationship(String userId, int relationship);

	/**
	 * 指定找到五级用户
	 * @param userId
	 * @return
	 */
	@Query(nativeQuery = true,value = "select supr.* from s_user_promote_relation supr LEFT JOIN s_user su on supr.parent_id = su.id where supr.user_id = ?1 and supr.parent_id != (SELECT referral_code from s_user where id = ?1) and supr.status is null and su.identity_code in (5,9,13) and supr.relationship > (SELECT IFNULL(MAX(relationship),0) from s_user_promote_relation where STATUS = 'Y' and user_id = ?1) ORDER BY supr.relationship LIMIT 0,1")
	UserPromoteRelation findByUserIdAndRelationship5(String userId);
	/**
	 * 指定找到九级用户
	 * @param userId
	 * @return
	 */
	@Query(nativeQuery = true,value = "select supr.* from s_user_promote_relation supr LEFT JOIN s_user su on supr.parent_id = su.id where supr.user_id = ?1 and supr.parent_id != (SELECT referral_code from s_user where id = ?1) and supr.status is null and su.identity_code  in (9,13) and supr.relationship > (SELECT IFNULL(MAX(relationship),0) from s_user_promote_relation where STATUS = 'Y' and user_id = ?1) ORDER BY supr.relationship LIMIT 0,1")
	UserPromoteRelation findByUserIdAndRelationship9(String userId);
	/**
	 * 指定找到十三级用户
	 * @param userId
	 * @return
	 */
	@Query(nativeQuery = true,value = "select supr.* from s_user_promote_relation supr LEFT JOIN s_user su on supr.parent_id = su.id where supr.user_id = ?1 and supr.parent_id != (SELECT referral_code from s_user where id = ?1) and supr.status is null and su.identity_code = 13 and supr.relationship > (SELECT IFNULL(MAX(relationship),0) from s_user_promote_relation where STATUS = 'Y' and user_id = ?1) ORDER BY supr.relationship LIMIT 0,1")
	UserPromoteRelation findByUserIdAndRelationship13(String userId);

	/**
	 * 查询团队中一星及以上用户
	 * @param userId
	 * @return
	 */
	@Query(nativeQuery = true,value = "SELECT count(1) from s_user_promote_relation supr LEFT JOIN s_user su on supr.user_id = su.id where supr.parent_id = ?1  and su.identity_code > 0 ")
	Integer findByParentIdAndRelationship(String userId);
}
